﻿



CREATE PROCEDURE [dbo].[proc_Employee_Search_AllUserName]
	(
		@CompanyId int,
		@DepartmentId int,
		@UserName nvarchar(50),
		@SearchText nvarchar(50)
	)
AS
begin		
		Declare @Pid int, @Cid int
		Select @Pid = Pid,@Cid=Cid From Employee Where UserName=@UserName
		
			declare @bmbh_T  varchar(50)
--			set @bmbh_T = (select bmbh  FROM department WHERE id=@DepartmentId)		
--			declare @indextable table(id int identity(1,1),nid int)
--			insert into @indextable(nid) select Id from department where bmbh like ''+@bmbh_T+'%' and bmbh is not null;
Select @bmbh_T=bmbh From department Where id=@departmentid

		Select
		Id,
		UserName,
		EName,
		CompanyId,
		DepartmentId,
		Isnull((Select PostName From Post Where Id=Employee.PostId),'...') As PostName,
		(Select CompanyName From Company Where Id=Employee.CompanyId) As CompanyName,
		Isnull((Select DName From Department Where Id=Employee.DepartmentId),'...') As DepartmentName,
		(Select Count(Customer.Id)  From Customer Where Customer.ProtectName=Employee.username and protectid=2) As UserPCount,
		(Select ProtectCount From Department Where Id=Employee.DepartmentId) As SystemPCount,
		ZhiWu,
		GradeId,
		(select Title from Grade where Id = GradeId) as GradeName,
		IsNull(PostCategory,0) as PostCategory,
		isnull((select Title from Post_Category where Post_Category.Id = PostCategory),'...') as PostCategoryName,
		PostId,
		Pluralist,
		(isnull((select Wage from Grade where id=Employee.GradeId),0)) as BasicWage,
		(isnull((select p.PostWage from Post p where p.id=Employee.PostId),0)) as PostWage,
		(datediff(mm,ISNULL(Employee.RuzhiDate,GetDate()),GetDate())/12*100) as WorkWage,
		(isnull((select JxWage from Post where id=Employee.PostId),0)) as JxWage,
		(isnull((select ManQin from Post where id=Employee.PostId),0)) as ManQin,
		ISNULL((Select Sum(EA.Amount) From Employee_Allowance EA Where EA.PostId=Employee.PostId and CategoryId=0 and Employee.Pid=EA.Pid and Employee.Cid=EA.Cid),0) as Allowance,
		IsNull((Select Sum(FA.Amount) From Employee_FixedAllowance FA Where FA.UserName = Employee.UserName),0) as FixedAllowance
		
		,ISNULL((select Title from Province where Id=@Pid),0) as Province,
		ISNULL((select Title from City where Id=@Cid),0) as City,
		ISNULL(Pid,0) as Pid,
		ISNULL(Cid,0) as Cid
		From Employee Where 
		(@CompanyId=0 or CompanyId=@CompanyId)
--		and (@DepartmentId=0 or DepartmentId in (select c.nid from @indextable c)) 
		and (@DepartmentId=0 or DepartmentId in ((Select Id From department Where Left(bmbh,Len(@bmbh_T))=@bmbh_T))) 
		and State <>5 
		and (@SearchText is null Or EName like '%'+@SearchText+'%')
		and UserName <> @UserName
		
end
	RETURN



